package com.sinobpo.rnd2.ehr.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.sinobpo.rnd2.ehr.dao.MedExpSatDao;
import com.sinobpo.rnd2.ehr.model.MedExpSat;
import com.sinobpo.rnd2.ehr.util.CommonUtil;
import com.sinobpo.rnd2.ehr.util.TimeType;

/**
 * 医疗费用统计数据接口实现类
 * 
 * @author yinghui.zhang
 * @CreateDate 2012-7-11
 * @file MedExpSatDaoImpl.java
 * @package com.sinobpo.rnd2.ehr.dao.impl
 * @version 1.0
 * @Email uuhui@163.com
 */
@Component("medExpSatDaoImpl")
@Transactional
public class MedExpSatDaoImpl implements MedExpSatDao {
	@Resource
	private SimpleJdbcTemplate simpleJdbcTemplate;

	/**
	 * @param conds
	 *            包括: 1.机构代码 2.开始日期 3.结束日期4.统计类型[门诊/住院] 5.病人类型分类[自费/医保/农保]
	 *            6.费用类型分类[诊疗/药品/其它]
	 */
	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public int getCount(TimeType timeType, Object... conds) {
		List<String> list = new ArrayList<String>();
		String sql = "SELECT COUNT(0) C FROM TBL_FEIYONGTJ WHERE 1=1 ";
		if (conds[0] != null && !conds[0].equals("")) {// 医疗机构代码不为空时的查询条件
			sql += " AND  JIGOUDM=? ";
			list.add((String) conds[0]);
		}
		sql += " AND TONGJILB=? ";// 统计类别(门诊,住院)
		list.add((String) conds[3]);// 统计类别
		if (conds[4] != null && !conds[4].equals("")) {// 病人汇总下的分类[自费,医保,农保]
			sql += " AND BINGRENLB=?";// 按病人类型汇总
			list.add((String) conds[4]);
		}
		if (conds[5] != null && !conds[5].equals("")) {// 费用类别汇总下的分类[[诊疗,药品,其它]
			sql += " AND FEIYONGLB=?"; // 按费用类别汇总
			list.add((String) conds[5]);
		}
		switch (timeType) {
		case DAY: {// 综合统计
			sql += " AND SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD') ";
			break;
		}
		case MONTH: {// 按月度统计
			sql += " AND TO_CHAR(SHANGBAORQ,'MM')>=? AND TO_CHAR(SHANGBAORQ,'MM')<=? ";
			sql += " GROUP BY JIGOUDM,TO_CHAR(SHANGBAORQ,'MM'),BINGRENLB,FEIYONGLB ";
			break;
		}
		case YEAR: {// 按年度统计
			sql += " AND TO_CHAR(SHANGBAORQ,'YYYY')>=? AND TO_CHAR(SHANGBAORQ,'YYYY')<=? ";
			sql += " GROUP BY JIGOUDM,TO_CHAR(SHANGBAORQ,'YYYY'),BINGRENLB,FEIYONGLB ";
			break;
		}
		}
		list.add((String) conds[1]);
		list.add((String) conds[2]);
		if (timeType.equals(TimeType.DAY))
			return simpleJdbcTemplate.queryForInt(sql, list.toArray());
		else
			return simpleJdbcTemplate.queryForList(sql, list.toArray()).size();
	}

	/**
	 * @param conds
	 *            包括: 1.机构代码 2.开始日期 3.结束日期4.统计类型[门诊/住院] 5.病人类型分类[自费/医保/农保]
	 *            6.费用类型分类[诊疗/药品/其它]
	 */
	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public List<MedExpSat> getList(int page, int rows, TimeType timeType,
			Object... conds) {
		String sql = null;
		String groupByColumn = null;
		sql = "SELECT * FROM (SELECT T.*, ROWNUM RN FROM ";
		List<String> list = new ArrayList<String>();
		switch (timeType) {
		case DAY: {// 综合统计
			sql += "(SELECT JIGOUDM,SHANGBAORQ,TONGJILB,BINGRENLB,FEIYONGLB,FEIYONGJE,FEIYONGMC  FROM TBL_FEIYONGTJ WHERE 1=1  ";
			sql += " AND SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD') ";
			break;
		}
		case MONTH: {// 按月度统计
			sql += "(SELECT JIGOUDM,TO_CHAR(SHANGBAORQ,'MM') SHANGBAORQ,TONGJILB,BINGRENLB,FEIYONGLB,SUM(FEIYONGJE) FEIYONGJE  FROM TBL_FEIYONGTJ WHERE 1=1  ";
			sql += " AND TO_CHAR(SHANGBAORQ,'MM')>=? AND TO_CHAR(SHANGBAORQ,'MM')<=? ";
			groupByColumn = " JIGOUDM,TO_CHAR(SHANGBAORQ,'MM') ,TONGJILB,BINGRENLB,FEIYONGLB ";
			break;
		}
		case YEAR: {// 按年度统计
			sql += "(SELECT JIGOUDM,TO_CHAR(SHANGBAORQ,'YYYY') SHANGBAORQ,TONGJILB,BINGRENLB,FEIYONGLB,SUM(FEIYONGJE) FEIYONGJE  FROM TBL_FEIYONGTJ WHERE 1=1  ";
			sql += " AND TO_CHAR(SHANGBAORQ,'YYYY')>=? AND TO_CHAR(SHANGBAORQ,'YYYY')<=? ";
			groupByColumn = " JIGOUDM,TO_CHAR(SHANGBAORQ,'YYYY') ,TONGJILB,BINGRENLB,FEIYONGLB ";
			break;
		}
		}
		list.add((String) conds[1]);
		list.add((String) conds[2]);
		if (conds[0] != null && !conds[0].equals("")) {// 医疗机构代码不为空时的查询条件
			sql += " AND  JIGOUDM=? ";
			list.add((String) conds[0]);
		}
		sql += " AND TONGJILB=? ";// 统计类别(门诊,住院)
		list.add((String) conds[3]);// 统计类别
		if (conds[4] != null && !conds[4].equals("")) {// 病人汇总下的分类[自费,医保,农保]
			sql += " AND BINGRENLB=?";// 按病人类型汇总
			list.add((String) conds[4]);
		}
		if (conds[5] != null && !conds[5].equals("")) {// 费用类别汇总下的分类[[诊疗,药品,其它]
			sql += " AND FEIYONGLB=?"; // 按费用类别汇总
			list.add((String) conds[5]);
		}
		if (groupByColumn == null)
			sql += "  ORDER BY SHANGBAORQ ) T ";
		else
			sql += "GROUP BY" + groupByColumn + "ORDER BY SHANGBAORQ ) T ";
		sql += "WHERE ROWNUM <= " + page * rows;
		sql += " ) WHERE RN >" + (page - 1) * rows;
		return simpleJdbcTemplate.query(sql,
				new ParameterizedRowMapper<MedExpSat>() {
					Map<String, String> map = CommonUtil.parseXml(false);

					@Override
					public MedExpSat mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						MedExpSat medExpSat = new MedExpSat();
						medExpSat.setOrgCode(rs.getString(1));
						medExpSat.setOrgName(map.get(rs.getString(1)));// 机构名称
						medExpSat.setRepDate(rs.getString(2));// 上报日期
						medExpSat.setCtype(rs.getString(3));// 统计类别[门诊/住院]
						medExpSat.setPtype(rs.getString(4));// 病人分类
						medExpSat.setEtype(rs.getString(5));// 费用分类
						medExpSat.setExpCount(rs.getDouble(6));// 费用金额
						medExpSat.setExpName(rs.getString(7));// 费用名称
						return medExpSat;
					}
				}, list.toArray());
	}

	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public List<MedExpSat> getSum(TimeType timeType, Object... conds) {
		String groupByColumn = null;
		String sql = null;

		switch (timeType) {
		case DAY:

			sql = "SELECT  SHANGBAORQ," + conds[0];
			sql += ",SUM(FEIYONGJE) FEIYONGJE  FROM TBL_FEIYONGTJ ";
			groupByColumn = "SHANGBAORQ";
			sql += "WHERE  SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD') ";
			break;
		case MONTH:
			sql = "SELECT TO_CHAR(SHANGBAORQ,'MM') SHANGBAORQ," + conds[0];
			sql += ",SUM(FEIYONGJE) FEIYONGJE  FROM TBL_FEIYONGTJ ";
			groupByColumn = "TO_CHAR(SHANGBAORQ,'MM')";
			sql += "WHERE TO_CHAR(SHANGBAORQ,'MM')>=? and TO_CHAR(SHANGBAORQ,'MM')<=?";
			break;
		case YEAR:
			sql = "SELECT TO_CHAR(SHANGBAORQ,'YYYY') SHANGBAORQ," + conds[0];
			sql += ",SUM(FEIYONGJE) FEIYONGJE  FROM TBL_FEIYONGTJ ";
			groupByColumn = "TO_CHAR(SHANGBAORQ,'YYYY')";
			sql += "WHERE TO_CHAR(SHANGBAORQ,'YYYY')>=? and TO_CHAR(SHANGBAORQ,'YYYY')<=?";
			break;
		}
		List<String> list = new ArrayList<String>();
		list.add((String) conds[2]);// /添加开始时间
		list.add((String) conds[3]);// /添加结束时间
		if (conds[1] != null && !"".equals(conds[1])) {// 机构代码约束
			sql += " AND JIGOUDM=?";
			list.add((String) conds[1]);
		}
		sql += " AND TONGJILB=? ";// 统计类别(门诊,住院)
		list.add((String) conds[4]);// 统计类别
		if (conds[6] != null && !"".equals(conds[6])) {// 费用汇总下的分类[自费,医保,农保],[诊疗,药品,其它]
			sql += "AND " + conds[5] + "=?";
			list.add((String) conds[6]);
		}
		sql += " GROUP BY  " + groupByColumn + ", " + conds[0] + " ORDER BY "
				+ conds[0];
		return simpleJdbcTemplate.query(sql,
				new ParameterizedRowMapper<MedExpSat>() {
					@Override
					public MedExpSat mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						MedExpSat medExpSat = new MedExpSat();
						medExpSat.setRepDate(rs.getString(1));
						medExpSat.setEtype(rs.getString(2));
						medExpSat.setPtype(rs.getString(2));
						medExpSat.setExpCount(rs.getDouble(3));
						return medExpSat;
					}
				}, list.toArray());
	}

	/**
	 * @param conds
	 *            包括: 1.费用汇总类型[按病人类型汇总,按费用类型汇总] 2.机构代码 3.开始日期4.结束日期
	 *            5.统计类别(门诊,住院) 6.费用汇总类型[按病人类型汇总,按费用类型汇总]
	 *            7.费用汇总下的分类([自费,医保,农保],[诊疗,药品,其它]) 注:费用汇总下的分类不存在默认按总类汇总(不分类)
	 */
	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public Object getSumByType(TimeType timeType, Object... conds) {// conds:
		String sql = "SELECT " + conds[0];
		sql += ",SUM(FEIYONGJE) FEIYONGJE  FROM TBL_FEIYONGTJ ";
		switch (timeType) {
		case DAY:// 按年月日期格式
			sql += "WHERE SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD')";
			break;
		case MONTH:
			sql += "WHERE TO_CHAR(SHANGBAORQ,'MM')>=? and TO_CHAR(SHANGBAORQ,'MM')<=?";
			break;
		case YEAR:
			sql += "WHERE TO_CHAR(SHANGBAORQ,'YYYY')>=? and TO_CHAR(SHANGBAORQ,'YYYY')<=?";
			break;
		}
		List<String> list = new ArrayList<String>();
		list.add((String) conds[2]);// /添加开始时间
		list.add((String) conds[3]);// /添加结束时间
		if (conds[1] != null && !"".equals(conds[1])) {// 机构代码约束
			sql += " AND JIGOUDM=?";
			list.add((String) conds[1]);
		}
		sql += " AND TONGJILB=? ";// 统计类别(门诊,住院)
		list.add((String) conds[4]);// 统计类别
		if (conds[6] != null && !"".equals(conds[6])) {// 费用汇总下的分类[自费,医保,农保],[诊疗,药品,其它]
			sql += "AND " + conds[5] + "=?";
			list.add((String) conds[6]);
		}
		sql += " GROUP BY " + conds[0] + " ORDER BY " + conds[0];
		List<Map<String, Object>> mapList = simpleJdbcTemplate.queryForList(
				sql, list.toArray());
		List<Object> objList = new ArrayList<Object>();
		if (mapList.size() > 0) {
			for (Map<String, Object> map : mapList) {
				objList.add((map.values().toArray()[1]).toString());
			}
			return objList.toArray();
		} else
			return new Object[3];
	}

	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public List<MedExpSat> getSumOfPCT(TimeType timeType, String viewName,
			Object... conds) {
		String groupByColumn = null;
		String sql = null;
		switch (timeType) {
		case DAY: {
			sql = "SELECT  SHANGBAORQ,";
			sql += " FEIYONGLB,ROUND( SUM(FEIYONGJE)/SUM(RS),2) FEIYONGJE  FROM "
					+ viewName;
			groupByColumn = "SHANGBAORQ";
			sql += " WHERE  SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD') ";
			break;
		}
		case MONTH: {
			sql = "SELECT TO_CHAR(SHANGBAORQ,'MM') SHANGBAORQ,";
			sql += " FEIYONGLB,ROUND( SUM(FEIYONGJE)/SUM(RS),2) FEIYONGJE  FROM "
					+ viewName;
			groupByColumn = "TO_CHAR(SHANGBAORQ,'MM')";
			sql += " WHERE TO_CHAR(SHANGBAORQ,'MM')>=? and TO_CHAR(SHANGBAORQ,'MM')<=?";
			break;
		}
		case YEAR: {

			sql = "SELECT TO_CHAR(SHANGBAORQ,'YYYY') SHANGBAORQ,";
			sql += " FEIYONGLB,ROUND( SUM(FEIYONGJE)/SUM(RS),2) FEIYONGJE  FROM "
					+ viewName;
			groupByColumn = " TO_CHAR(SHANGBAORQ,'YYYY')";
			sql += " WHERE TO_CHAR(SHANGBAORQ,'YYYY')>=? and TO_CHAR(SHANGBAORQ,'YYYY')<=?";
			break;
		}
		}
		List<String> list = new ArrayList<String>();
		list.add((String) conds[1]);// /添加开始时间
		list.add((String) conds[2]);// /添加结束时间
		if (conds[0] != null && !"".equals(conds[0])) {// 机构代码约束
			sql += " AND JIGOUDM=?";
			list.add((String) conds[0]);
		}
		sql += " GROUP BY FEIYONGLB, " + groupByColumn;
		sql += " ORDER BY FEIYONGLB";
		return simpleJdbcTemplate.query(sql,
				new ParameterizedRowMapper<MedExpSat>() {
					@Override
					public MedExpSat mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						MedExpSat medExpSat = new MedExpSat();
						medExpSat.setRepDate(rs.getString(1));
						medExpSat.setEtype(rs.getString(2));
						medExpSat.setExpPCTCount(rs.getDouble(3));
						return medExpSat;
					}
				}, list.toArray());

	}

	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public Object getSumByTypeOfPCT(TimeType timeType, String viewName,
			Object... conds) {
		String sql = "SELECT FEIYONGLB, ROUND(SUM(FEIYONGJE)/sum(RS),2) FEIYONGJE FROM "
				+ viewName;
		switch (timeType) {
		case DAY:
			sql += " WHERE  SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD') ";
			break;
		case MONTH:
			sql += " WHERE TO_CHAR(SHANGBAORQ,'MM')>=? and TO_CHAR(SHANGBAORQ,'MM')<=?";
			break;
		case YEAR:
			sql += " WHERE TO_CHAR(SHANGBAORQ,'YYYY')>=? and TO_CHAR(SHANGBAORQ,'YYYY')<=?";
			break;
		}
		List<String> list = new ArrayList<String>();
		list.add((String) conds[1]);
		list.add((String) conds[2]);
		if (conds[0] != null && !"".equals(conds[0])) {
			sql += " AND JIGOUDM=?";
			list.add((String) conds[0]);
		}
		sql += " GROUP BY FEIYONGLB ORDER BY FEIYONGLB";
		List<Map<String, Object>> mapList = simpleJdbcTemplate.queryForList(
				sql, list.toArray());
		List<Object> objList = new ArrayList<Object>();
		if (mapList.size() > 0) {
			for (Map<String, Object> map : mapList) {
				objList.add((map.values().toArray()[1]).toString());
			}
			return objList.toArray();
		} else
			return new Object[3];

	}

	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public List<MedExpSat> getListOfPCT(int page, int rows, TimeType timeType,
			String viewName, Object... conds) {
		String sql = "SELECT * FROM (SELECT T.*, ROWNUM RN FROM ";
		String groupByColumn = null;
		List<String> list = new ArrayList<String>();

		switch (timeType) {
		case DAY: {// 综合统计
			sql += "(SELECT JIGOUDM,SHANGBAORQ,FEIYONGLB,ROUND(SUM(FEIYONGJE)/SUM(RS),2) FEIYONGJE FROM "
					+ viewName + " WHERE 1=1  ";
			sql += " AND SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD') ";
			groupByColumn = "JIGOUDM,SHANGBAORQ,FEIYONGLB ";
			break;
		}
		case MONTH: {// 按月度统计
			sql += "(SELECT JIGOUDM,TO_CHAR(SHANGBAORQ,'MM') SHANGBAORQ,FEIYONGLB,ROUND(SUM(FEIYONGJE)/SUM(RS),2) FEIYONGJE FROM "
					+ viewName + " WHERE 1=1  ";
			sql += " AND TO_CHAR(SHANGBAORQ,'MM')>=? AND TO_CHAR(SHANGBAORQ,'MM')<=? ";
			groupByColumn = "JIGOUDM,TO_CHAR(SHANGBAORQ,'MM'),FEIYONGLB";
			break;
		}
		case YEAR: {// 按年度统计
			sql += "(SELECT JIGOUDM,TO_CHAR(SHANGBAORQ,'YYYY') SHANGBAORQ,FEIYONGLB,ROUND(SUM(FEIYONGJE)/SUM(RS),2) FEIYONGJE FROM "
					+ viewName + " WHERE 1=1  ";
			sql += " AND TO_CHAR(SHANGBAORQ,'YYYY')>=? AND TO_CHAR(SHANGBAORQ,'YYYY')<=? ";
			groupByColumn = "JIGOUDM,TO_CHAR(SHANGBAORQ,'YYYY'),FEIYONGLB";
			break;
		}
		}
		list.add((String) conds[1]);
		list.add((String) conds[2]);
		if (conds[0] != null && !conds[0].equals("")) {// 医疗机构代码不为空时的查询条件
			sql += " AND  JIGOUDM=? ";
			list.add((String) conds[0]);
		}
		sql += "  GROUP BY " + groupByColumn + "  ORDER BY SHANGBAORQ ) T ";
		sql += "WHERE ROWNUM <= " + page * rows;
		sql += " ) WHERE RN >" + (page - 1) * rows;
		return simpleJdbcTemplate.query(sql,
				new ParameterizedRowMapper<MedExpSat>() {
					Map<String, String> map = CommonUtil.parseXml(false);

					@Override
					public MedExpSat mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						MedExpSat medExpSat = new MedExpSat();
						medExpSat.setOrgCode(rs.getString(1));
						medExpSat.setOrgName(map.get(rs.getString(1)));// 机构名称
						medExpSat.setRepDate(rs.getString(2));// 上报日期
						medExpSat.setEtype(rs.getString(3));// 费用分类
						medExpSat.setExpPCTCount(rs.getDouble(4));// 费用金额
						return medExpSat;
					}
				}, list.toArray());

	}

	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public int getCountOfPCT(TimeType timeType, String viewName,
			Object... conds) {
		String sql = "SELECT COUNT(0) C FROM " + viewName + " WHERE 1=1 ";
		List<String> list = new ArrayList<String>();
		if (conds[0] != null && !conds[0].equals("")) {// 医疗机构代码不为空时的查询条件
			sql += " AND  JIGOUDM=? ";
			list.add((String) conds[0]);
		}
		switch (timeType) {
		case DAY: {// 综合统计
			sql += " AND SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD') ";
			break;
		}
		case MONTH: {// 按月度统计
			sql += " AND TO_CHAR(SHANGBAORQ,'MM')>=? AND TO_CHAR(SHANGBAORQ,'MM')<=? ";
			sql += " GROUP BY JIGOUDM,TO_CHAR(SHANGBAORQ,'MM'),FEIYONGLB ";
			break;
		}
		case YEAR: {// 按年度统计
			sql += " AND TO_CHAR(SHANGBAORQ,'YYYY')>=? AND TO_CHAR(SHANGBAORQ,'YYYY')<=? ";
			sql += " GROUP BY JIGOUDM,TO_CHAR(SHANGBAORQ,'YYYY'),FEIYONGLB ";
			break;
		}
		}
		list.add((String) conds[1]);
		list.add((String) conds[2]);
		if (timeType.equals(TimeType.DAY))
			return simpleJdbcTemplate.queryForInt(sql, list.toArray());
		else
			return simpleJdbcTemplate.queryForList(sql, list.toArray()).size();
	}
}
